package ro.gateway.aida.fnd;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import ro.gateway.aida.admin.PropertyListDB;
import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.obj.GenericActivityProperty;
import ro.gateway.aida.obj.db.AIDAActivityObjectDB;
import ro.gateway.aida.obj.db.GenericActivityPropertyDB;
import ro.gateway.aida.obj.db.OrganizationDB;
import ro.gateway.aida.srv.IIDGenerator;

/**
 * <p>Title: Romanian AIDA</p> <p>Description: </p> <p>Copyright: Copyright (comparator) 2003</p> <p>Company:
 * ro-gateway</p>
 *
 * @deprecated
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: FundingDB.java,v 1.1 2004/10/24 23:37:13 mihaipostelnicu Exp $
 */
public class FundingDB extends DBPersistenceManager {
	private FundingDB(PersistenceToken token) {
		super(token);
	}

	public static FundingDB getManager(PersistenceToken token) {
		return new FundingDB(token);
	}

	public void insert(IIDGenerator idGenerators,
				    FundingItem item)
		 throws SQLException {

		AIDAActivityObjectDB.getManager(token).insert(idGenerators, item);
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_FND_ITEM);
			ps.setLong(1, item.getId());
			long org_id = item.getOrg_id();
			if (org_id == -1) {
				ps.setNull(2, Types.INTEGER);
			} else {
				ps.setLong(2, org_id);
			}
			ps.setLong(3, item.getType_id());
			ps.setDouble(4, item.getAmount());
			ps.setString(5, item.getCurrency());
			ps.setInt(6, (int) item.getStatus_id());
			ps.executeUpdate();
			ps.close();

//               FundingYearlyItem[] yitems = item.getYearlyItemsArray();
//               if ( yitems == null )
//                    yitems = new FundingYearlyItem[ 0 ];
//               for ( int i = 0; i < yitems.length; i++ ) {
//                    yitems[ i ].setFunding_id( item.getId() );
//                    insert_yearly( con, yitems[ i ] );
//               }
//
//               FundingByCatItem[] byCatItems = item.getByCatItemsArray();
//               if ( byCatItems == null )
//                    byCatItems = new FundingByCatItem[ 0 ];
//               for ( int i = 0; i < byCatItems.length; i++ ) {
//                    byCatItems[ i ].setFunding_id( item.getId() );
//                    insert_by_cat( byCatItems[ i ] );
//               }
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	private void insert_by_cat(FundingByCatItem item)
		 throws SQLException {

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_FND_BY_CAT);

			ps.setLong(1, item.getFunding_id());
			ps.setLong(2, item.getComponent_id());
			ps.setLong(3, item.getAmount());
			ps.setString(4, item.getCurrency());

			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		FundingYearlyItem[] yitems = item.getYearlyItemsArray();
		if (yitems == null)
			yitems = new FundingYearlyItem[0];

		for (int i = 0; i < yitems.length; i++) {
			yitems[i].setFunding_id(item.getFunding_id());
			insert_yearly(con, yitems[i]);
		}
	}

	private static final void insert_yearly(Connection con,
									FundingYearlyItem item)
		 throws SQLException {

		PreparedStatement ps = con.prepareStatement(INSERT_FND_YEARLY);

		ps.setLong(1, item.getFunding_id());
		if (item.getComponent_id() != -1) {
			ps.setLong(2, item.getComponent_id());
		} else {
			ps.setNull(2, Types.INTEGER);
		}
		ps.setDate(3, new Date(item.getStart_date()));
		ps.setLong(4, item.getAmount());
		ps.setString(5, item.getCurrency());

		ps.executeUpdate();
		ps.close();
	}

	public FundingItem[] getFundingsByActivity(long activity_id)
		 throws SQLException {

		Connection con = getConnection();
		FundingItem[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_FUNDINGS_BY_ACT);
			ps.setLong(1, activity_id);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FundingItem item = new FundingItem();
				item.setId(rs.getLong(1));
				item.setOrg(OrganizationDB.getManager(token).getByID(rs.getLong(2)));
				item.setAmount(rs.getDouble(3));
				item.setCurrency(rs.getString(4));
				item.setStatus(PropertyListDB.getManager(db_token).getItemById(rs.getLong(5)));
//				item.setStatus(FundingStatusDB.getManager(token).getById(rs.getLong(5)));

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new FundingItem[items.size()];
			items.toArray(result);

//               for ( int i = 0; i < result.length; i++ ) {
//                    FundingByCatItem[] byCatItems =
//                       getByCatItems( result[ i ].getId() );
//                    if ( byCatItems == null )
//                         byCatItems = new FundingByCatItem[ 0 ];
//                    for ( int j = 0; j < byCatItems.length; j++ ) {
//                         result[ i ].addByCatItem( byCatItems[ j ] );
//                    }
//
//                    FundingYearlyItem[] yearlyItems =
//                       getYearlyItems( result[ i ].getId(), -1 );
//                    if ( yearlyItems == null )
//                         yearlyItems = new FundingYearlyItem[ 0 ];
//                    for ( int j = 0; j < yearlyItems.length; j++ ) {
//                         result[ i ].addYearlyItem( yearlyItems[ j ] );
//                    }
//               }
		}

		return result;
	}

	public FundingItem[] getFundingsIDSByActivity(long activity_id)
		 throws SQLException {

		Connection con = getConnection();
		FundingItem[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_FUNDINGS_BY_ACT);
			ps.setLong(1, activity_id);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FundingItem item = new FundingItem();
				item.setId(rs.getLong(1));
				item.setOrg(OrganizationDB.getManager(token).getByID(rs.getLong(2)));
				item.setAmount(rs.getDouble(3));
				item.setCurrency(rs.getString(4));
				item.setStatus(PropertyListDB.getManager(db_token).getItemById(rs.getLong(5)));
//				item.setStatus(FundingStatusDB.getManager(token).getById(rs.getLong(5)));

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new FundingItem[items.size()];
			items.toArray(result);
		}

		return result;
	}

	private FundingByCatItem[] getByCatItems(long funding_id)
		 throws SQLException {

		Connection con = getConnection();
		ArrayList items = new ArrayList();
		FundingByCatItem[] result = null;

		try {
			PreparedStatement ps = null;
			ps = con.prepareStatement(GET_FND_BYCAT);
			ps.setLong(1, funding_id);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FundingByCatItem item = new FundingByCatItem();
				item.setFunding_id(funding_id);

				long component_id = rs.getLong(1);
				if ((component_id == -1) || rs.wasNull()) {
					item.setComponent(new GenericActivityProperty());
					item.getComponent().setName("en", "DB ERROR");
				} else {
					item.setComponent(GenericActivityPropertyDB.getManager(token).getById("gen_components",
						 component_id));
				}
				item.setAmount(rs.getLong(2));
				item.setCurrency(rs.getString(3));

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new FundingByCatItem[items.size()];
			items.toArray(result);

			for (int i = 0; i < result.length; i++) {
				FundingYearlyItem[] yitems =
					 getYearlyItems(funding_id,
						  result[i].getComponent_id());
				if (yitems == null)
					yitems = new FundingYearlyItem[0];
				for (int j = 0; j < yitems.length; j++) {
					result[i].addYearlyItem(yitems[j]);
				}
			}
		}

		return result;
	}

	private FundingYearlyItem[] getYearlyItems(long funding_id,
									   long category_id)
		 throws SQLException {

		Connection con = getConnection();
		ArrayList items = new ArrayList();
		FundingYearlyItem[] result = null;

		GenericActivityProperty zacomponent = null;
		if (category_id != -1) {
			zacomponent =
				 GenericActivityPropertyDB.getManager(token).getById("gen_components",
					  category_id);
		}

		try {
			PreparedStatement ps = null;
			if (category_id == -1) {
				ps = con.prepareStatement(GET_FND_YEARLY);
				ps.setLong(1, funding_id);
			} else {
				ps = con.prepareStatement(GET_FND_YEARLY_BY_CAT);
				ps.setLong(1, funding_id);
				ps.setLong(2, category_id);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FundingYearlyItem item = new FundingYearlyItem();
				item.setFunding_id(funding_id);

				if (category_id == -1) {
					item.setComponent(null);
				} else {
					item.setComponent(zacomponent);
				}
				item.setStart_date(rs.getDate(2).getTime());
				item.setAmount(rs.getLong(3));
				item.setCurrency(rs.getString(4));

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new FundingYearlyItem[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public void delete(long id)
		 throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = null;
//               ps = con.prepareStatement( DELETE_YEARLY_FOR_FND );
//               ps.setLong( 1, id );
//               ps.executeUpdate();
//               ps.close();
//
//               ps = con.prepareStatement( DELETE_BYCAT_FOR_FND );
//               ps.setLong( 1, id );
//               ps.executeUpdate();
//               ps.close();

			ps = con.prepareStatement(DELETE_FND);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	private void deleteFundingsObjForActivity(long activity_id)
		 throws SQLException {

		Connection con = getConnection();

		try {
			PreparedStatement ps =
				 con.prepareStatement("DELETE FROM objects "
				 + "WHERE activity_id=? AND (obj_type="
				 + AIDAActivityObject.TYPE_FUNDING
				 + " OR obj_type="
				 + AIDAActivityObject.TYPE_FUNDING_BYCAT
				 + " OR obj_type="
				 + AIDAActivityObject.TYPE_FUNDING_YEARLY
				 + ")");
			ps.setLong(1, activity_id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void deleteForActivity(long activity_id)
		 throws SQLException {

		FundingItem[] fitems =
			 getFundingsIDSByActivity(activity_id);
		if (fitems != null) {
			for (int i = 0; i < fitems.length; i++) {
				delete(fitems[i].getId());
			}
		}
		deleteFundingsObjForActivity(activity_id);
	}


	private static final String INSERT_FND_ITEM =
		 "INSERT INTO funding (id, org_id, type, amount, harm_currency, status) "
		 + "VALUES (?,?,?,?,?,?)";

	private static final String INSERT_FND_YEARLY =
		 "INSERT INTO funding_yearly (funding_id, category_id, date_start, "
		 + "amount, harm_currency) "
		 + "VALUES (?,?,?,?,?)";

	private static final String INSERT_FND_BY_CAT =
		 "INSERT INTO funding_bycat (funding_id, category_id, amount, harm_currency) "
		 + "VALUES (?,?,?,?)";

	private static final String GET_FUNDINGS_BY_ACT =
		 "SELECT f.id, f.org_id, f.amount, f.harm_currency, f.status "
		 + "FROM funding f, objects o "
		 + "WHERE o.activity_id = ? AND f.id = o.id";

	/**
	 * le ia si pe alea din categorii ;) mutam logica in cod...
	 */
	private static final String GET_FND_YEARLY =
		 "SELECT category_id, date_start, amount, harm_currency "
		 + "FROM funding_yearly "
		 + "WHERE funding_id=? AND category_id IS NULL";
	private static final String GET_FND_YEARLY_BY_CAT =
		 "SELECT category_id, date_start, amount, harm_currency "
		 + "FROM funding_yearly "
		 + "WHERE funding_id=? AND category_id=?";

	private static final String GET_FND_BYCAT =
		 "SELECT category_id, amount, harm_currency "
		 + "FROM funding_bycat "
		 + "WHERE funding_id = ?";

	private static final String DELETE_FND = "DELETE FROM funding WHERE id = ?";
	private static final String DELETE_YEARLY_FOR_FND =
		 "DELETE FROM funding_yearly WHERE funding_id=?";
	private static final String DELETE_BYCAT_FOR_FND =
		 "DELETE FROM funding_bycat WHERE funding_id=?";
}
